"""
Copyright (c) 2023 - present SKbioinfo
"""

'''
    SQL: month interval query (Time range: three months)
    :param name: table name abbreviation
    :param col: selected column name
    :param table_name: query table name
    :param group_col: column for group by
'''

def three_months_interval_group_query(name:str,col:str,table_name:str,group_col:str,month_time:str):
    search_sql = ""
    ### SQL: month intervals search sql ###
    search_sql = '''
    SELECT {group_col},SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '30' DAY) AND \"{month_time}\" 
    THEN 1 ELSE 0  END) AS "[0 ~ 30天]",
    SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '60' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '30' DAY) 
    THEN 1 ELSE 0  END) AS "[30 ~ 60天]",
    SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '90' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '60' DAY) 
    THEN 1 ELSE 0  END) AS "[60 ~ 90天]"
    FROM {table_name} as {name} GROUP BY {group_col}
    '''.format(
        name=name,
        table_name=table_name,
        col=col,
        month_time=month_time,
        group_col=group_col
    )
    return search_sql

'''
    SQL: six days interval group query (Time range: one month)
    :param name: table name abbreviation
    :param col: selected column name
    :param table_name: query table name 
'''
def six_intervals_group_sql(name:str,col:str,month_time:str,table_name:str,group_col:str):
    search_sql = ""
    ### SQL: six days intervals search sql ### 
    search_sql = '''
    SELECT  {group_col},SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '6' DAY) AND \"{month_time}\"
                THEN 1 ELSE 0  END) AS "[0 ~ 6天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '12' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '6' DAY)
                THEN 1 ELSE 0  END) AS "[6 ~ 12天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '18' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '12' DAY)
                THEN 1 ELSE 0  END) AS "[12 ~ 18天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '24' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '18' DAY)
                THEN 1 ELSE 0  END) AS "[18 ~ 24天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '30' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '24' DAY)
                THEN 1 ELSE 0  END) AS "[24 ~ 30天]"
        FROM {table_name} as {name} GROUP BY {group_col}
    '''.format(
            name=name,
            col=col,
            month_time=month_time,
            table_name=table_name,
            group_col=group_col
    )
    return search_sql

'''
    SQL: month interval query (Time range: three months)
    :param name: table name abbreviation
    :param col: selected column name
    :param table_name: query table name  
'''
def three_months_interval_query(name:str,col:str,table_name:str,month_time:str):
    search_sql = ""
    ### SQL: month intervals search sql ###
    search_sql = '''
    SELECT SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '30' DAY) AND \"{month_time}\"
    THEN 1 ELSE 0  END) AS "[0 ~ 30天]",
    SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '60' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '30' DAY) 
    THEN 1 ELSE 0  END) AS "[30 ~ 60天]",
    SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '90' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '60' DAY) 
    THEN 1 ELSE 0  END) AS "[60 ~ 90天]"
    FROM {table_name} as {name} 
    '''.format(
        month_time=month_time,
        name=name,
        table_name=table_name,
        col=col
    )
    return search_sql

'''
    SQL: six days interval query (Time range: one month)
    :param name: table name abbreviation
    :param col: selected column name
    :param table_name: query table name  
'''
def six_days_interval_query(name:str,col:str,table_name:str,month_time:str):
    search_sql = ""
    ### SQL: six days intervals search sql ### 
    search_sql = '''
    SELECT
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '6' DAY) AND \"{month_time}\"
                THEN 1 ELSE 0  END) AS "[0 ~ 6天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '12' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '6' DAY)
                THEN 1 ELSE 0  END) AS "[6 ~ 12天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '18' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '12' DAY)
                THEN 1 ELSE 0  END) AS "[12 ~ 18天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '24' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '18' DAY)
                THEN 1 ELSE 0  END) AS "[18 ~ 24天]",
            SUM(CASE WHEN {name}.{col} BETWEEN  DATE_SUB(\"{month_time}\", INTERVAL '30' DAY) AND DATE_SUB(\"{month_time}\", INTERVAL '24' DAY)
                THEN 1 ELSE 0  END) AS "[24 ~ 30天]"
        FROM {table_name} as {name} 
    '''.format(
            month_time=month_time,
            name=name,
            col=col,
            table_name=table_name
    )
    return search_sql

'''
    SQL: sum up table column
    :param col: selected column name
    :param table_name: query table name  
'''
def sum_up_table_column(col:str,table_name:str):
    search_sql = ""
    search_sql = '''
    SELECT COUNT(DISTINCT {col}) 
    FROM {table_name}
    '''.format(
        col = col,
        table_name = table_name
    )
    return search_sql

'''
    SQL: show-up column information
    :param col: selected column name
    :param table_name: query table name  
'''
def total_query_sql(col:str,table_name:str):
    search_sql = ""
    search_sql='''
    SELECT
        {col}
    FROM {table_name}
    '''.format(
        col = col,
        table_name=table_name
    )
    return search_sql

def check_tuple_values(array:tuple):
    for content in array:
        for con in content:
            if con is None:
                return False
            else:
                return True